USE OnlineInsurance
GO

--------------------------News Store Procedure-----------------------------------------------

CREATE PROCEDURE GetAllNews
AS
SELECT * FROM News
GO

CREATE PROCEDURE GetNewsById
	@NewsId INT
AS
SELECT * FROM News
WHERE NewsId = @NewsId
GO

CREATE PROCEDURE InsertNews
	@Title NVARCHAR(256),
	@Content NTEXT,
	@CreateDate	DATETIME
AS
INSERT INTO News
VALUES (@Title,@Content,@CreateDate)
GO

CREATE PROCEDURE UpdateNewsById
	@NewsId INT,
	@Title NVARCHAR(256),
	@Content NTEXT	
AS
UPDATE News
SET Title = @Title,
	[Content] = @Content	
WHERE NewsId = @NewsId
GO

CREATE PROCEDURE DeleteNewsById
	@NewsId INT
AS
DELETE FROM News
WHERE NewsId = @NewsId
GO

-------------------------Insurance Store Procedure-------------------------------------------

CREATE PROCEDURE GetAllInsurance
AS
SELECT * FROM Insurance
GO

CREATE PROCEDURE GetInsuranceById
	@InsuranceId INT
AS
SELECT * FROM Insurance
WHERE InsuranceId = @InsuranceId
GO

CREATE PROCEDURE GetInsuranceIdByInsuranceType
	@InsuranceType NVARCHAR(50)
AS
SELECT InsuranceId FROM Insurance
WHERE InsuranceType = @InsuranceType
GO


CREATE PROCEDURE InsertInsurance
	@InsuranceType NVARCHAR(50),
	@Description NTEXT
AS
INSERT INTO Insurance
VALUES (@InsuranceType, @Description)
GO

CREATE PROCEDURE UpdateInsuranceById
	@InsuranceID INT,
	@InsuranceType NVARCHAR(50),
	@Description NTEXT	
AS
UPDATE Insurance
SET InsuranceType = @InsuranceType,
	[Description] = @Description
WHERE InsuranceID = @InsuranceID
GO

CREATE PROCEDURE DeleteInsuranceById
	@InsuranceID INT
AS
DELETE FROM Insurance
WHERE InsuranceID = @InsuranceID
GO

--------------------------Value Store Procedure-----------------------------------------------

CREATE PROCEDURE GetAllValue
AS
SELECT * FROM [Value]
GO

CREATE PROCEDURE GetValueById
	@ValueId INT
AS
SELECT [Value].ValueId,Insurance.InsuranceType,[Value].ValueType,[Value].Price FROM [Value] 
       join Insurance on [Value].InsuranceId=Insurance.InsuranceId				
WHERE ValueId = @ValueId
GO

CREATE PROCEDURE GetValueByInsuranceId
	@InsuranceId INT
AS
SELECT * FROM [Value]
WHERE InsuranceId = @InsuranceId
GO

CREATE PROCEDURE InsertValue
	@InsuranceId INT,
	@ValueType NVARCHAR(50),
	@Price	INT
AS
INSERT INTO [Value]
VALUES (@InsuranceId, @ValueType,@Price)
GO

CREATE PROCEDURE UpdateValueById
	@ValueId INT,
	@ValueType NVARCHAR(50),
	@Price INT
AS
UPDATE [Value]
SET ValueType = @ValueType,
	Price = @Price
WHERE ValueId = @ValueId
GO

CREATE PROCEDURE DeleteValueById
	@ValueId INT
AS
DELETE FROM [Value]
WHERE ValueId = @ValueId
GO

CREATE PROCEDURE DeleteValueByInsuranceId
	@InsuranceId INT
AS
DELETE FROM [Value]
WHERE InsuranceId = @InsuranceId
GO

--------------------------Policy Store Procedure-----------------------------------------------

CREATE PROCEDURE GetAllPolicy
AS
SELECT * FROM Policy
GO

CREATE PROCEDURE GetPolicyById
	@PolicyId INT
AS
SELECT * FROM Policy
WHERE PolicyId = @PolicyId
GO

CREATE PROCEDURE GetPolicyByUserId
	@UserId INT
AS
SELECT * FROM Policy
WHERE UserId = @UserId
GO

CREATE PROCEDURE GetPolicyIsPaid
AS
SELECT * FROM Policy
WHERE Paid = 1
GO

CREATE PROCEDURE GetPolicyNotPaid
AS
SELECT * FROM Policy
WHERE Paid = 0
GO

CREATE PROCEDURE InsertPolicy
	@UserId INT,
	@InsuranceType NVARCHAR(50),
	@PolicyValue INT,
	@Period NVARCHAR(50),
	@Premium FLOAT,
	@ContractDate DATETIME,
	@Paid BIT
AS
INSERT INTO Policy
VALUES (@UserId,@InsuranceType,@PolicyValue,@Period,@Premium,@ContractDate,@Paid)
GO

CREATE PROCEDURE UpdatePolicyPaidById
	@PolicyId int
AS
UPDATE Policy
SET Paid = 1
WHERE PolicyId = @PolicyId
GO

CREATE PROCEDURE DeletePolicyById
	@PolicyId INT
AS
DELETE FROM Policy
WHERE PolicyId = @PolicyId
GO

CREATE PROCEDURE DeletePolicyByUserId
	@UserId INT
AS
DELETE FROM Policy
WHERE UserId = @UserId
GO

--------------------------User Store Procedure-----------------------------------------------

CREATE PROCEDURE GetAllUser
AS
SELECT * FROM [User]
WHERE [Role] = 'User'
GO

CREATE PROCEDURE GetUserByUsername
	@Username NVARCHAR(50)
AS
SELECT * FROM [User]
WHERE Username = @Username
GO

CREATE PROCEDURE GetUserById
	@UserId INT
AS
SELECT * FROM [User]
WHERE UserId = @UserId
GO

CREATE PROCEDURE InsertUser
	@Username NVARCHAR(50),
	@Password NVARCHAR(50),
	@Fullname NVARCHAR(100),
	@Gender NVARCHAR(50),
	@Address NVARCHAR(50),
	@Role NVARCHAR(50),
	@Phone NVARCHAR(50),
	@Email NVARCHAR(50)
AS
INSERT INTO [User]
VALUES (@Username,@Password,@Fullname,@Gender,@Address,@Role,@Phone,@Email)
GO

CREATE PROCEDURE UpdatePasswordByUsername
	@Username NVARCHAR(50),
	@Password NVARCHAR(50)
AS
UPDATE [User]
SET [Password] = @Password
WHERE Username = @Username
GO

CREATE PROCEDURE UpdateProfileByUsername
	@Username NVARCHAR(50),
	@Fullname NVARCHAR(100),
	@Gender NVARCHAR(50),
	@Address NVARCHAR(50),
	@Phone NVARCHAR(50),
	@Email NVARCHAR(50)
AS
UPDATE [User]
SET Fullname = @Fullname,
	Gender = @Gender,
	[Address] = @Address,
	Phone = @Phone,
	Email = @Email
WHERE Username = @Username
GO

CREATE PROCEDURE DeleteUserById
	@UserId INT
AS
DELETE FROM [User]
WHERE UserId = @UserId
GO

--------------------------Bill Store Procedure-----------------------------------------------

CREATE PROCEDURE GetAllBill
AS
SELECT * FROM Bill
GO

CREATE PROCEDURE GetBillById
	@BillId INT
AS
SELECT * FROM Bill
WHERE BillId = @BillId
GO

CREATE PROCEDURE GetBillIsPaid
AS
SELECT * FROM Bill
WHERE IsPaid = 1
GO

CREATE PROCEDURE GetBillNotPaid
AS
SELECT * FROM Bill
WHERE IsPaid = 0
GO

CREATE PROCEDURE InsertBill
	@PolicyId INT,
	@Cost FLOAT,
	@Note NVARCHAR(50),
	@DateOfCreate DATETIME,
	@IsPaid BIT
AS
INSERT INTO Bill
VALUES (@PolicyId,@Cost,@Note,@DateOfCreate,@IsPaid)
GO

CREATE PROCEDURE UpdateBillPaidById
	@BillId int
AS
UPDATE Bill
SET IsPaid = 1
WHERE BillId = @BillId
GO

CREATE PROCEDURE DeleteBillById
	@BillId INT
AS
DELETE FROM Bill
WHERE BillId = @BillId
GO

CREATE PROCEDURE DeleteBillByPolicyId
	@PolicyId INT
AS
DELETE FROM Bill
WHERE PolicyId = @PolicyId
GO


